{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "a3cf662c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>253</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>256</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>249</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>261</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>224</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>468</th>\n",
       "      <td>471</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>298</td>\n",
       "      <td>8.76</td>\n",
       "      <td>200.0</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>4533</td>\n",
       "      <td>169.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>469</th>\n",
       "      <td>472</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>263</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>473</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>319</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>7042</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>474</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>205</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>5755</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>475</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>279</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>5688</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>473 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index  班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0        0   1  男      253   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1        1   1  男      256   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2        2   1  男      249   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3        3   1  男      261   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4        4   1  男      224   7.52  210.0    13    9  3538  171.0  54.7    0\n",
       "..     ...  .. ..      ...    ...    ...   ...  ...   ...    ...   ...  ...\n",
       "468    471  17  男      298   8.76  200.0    12    9  4533  169.0  51.3    0\n",
       "469    472  17  男      263   8.27  208.0    10    0  4647  176.0  69.5    0\n",
       "470    473  17  男      319   9.55  210.0    15    6  7042  177.0  76.0    0\n",
       "471    474  17  男      205   7.50  252.0    13   13  5755  181.0  65.0    0\n",
       "472    475  17  男      279   7.81  208.0    14   11  5688  172.0  51.7    0\n",
       "\n",
       "[473 rows x 12 columns]"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "df1 = pd.read_excel('./18级高一体测成绩汇总.xls')\n",
    "df1.drop_duplicates(inplace = True) # 删除重复数据\n",
    "df1.reset_index(inplace=True) # ⾏索引 重置\n",
    "field_list = df1.columns.tolist()\n",
    "def convert_str(x):\n",
    " if type(x) == int:\n",
    "        return x\n",
    " else :\n",
    "     m,s=x.split(\"'\")\n",
    "     return int(m)*60+int(s)        \n",
    "df1[\"男1000米跑\"] = data[\"男1000米跑\"].apply(convert_str)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "6a24a9b2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女50米跑</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女跳远</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男引体</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>210</td>\n",
       "      <td>100</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>215</td>\n",
       "      <td>95</td>\n",
       "      <td>210</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>220</td>\n",
       "      <td>90</td>\n",
       "      <td>216</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>227</td>\n",
       "      <td>85</td>\n",
       "      <td>223</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>235</td>\n",
       "      <td>80</td>\n",
       "      <td>230</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>3680</td>\n",
       "      <td>78</td>\n",
       "      <td>2650</td>\n",
       "      <td>78</td>\n",
       "      <td>7.7</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>78</td>\n",
       "      <td>13.6</td>\n",
       "      <td>...</td>\n",
       "      <td>175</td>\n",
       "      <td>78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>240</td>\n",
       "      <td>78</td>\n",
       "      <td>235</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>3560</td>\n",
       "      <td>76</td>\n",
       "      <td>2550</td>\n",
       "      <td>76</td>\n",
       "      <td>7.9</td>\n",
       "      <td>76</td>\n",
       "      <td>9.0</td>\n",
       "      <td>76</td>\n",
       "      <td>12.2</td>\n",
       "      <td>...</td>\n",
       "      <td>172</td>\n",
       "      <td>76</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76</td>\n",
       "      <td>39</td>\n",
       "      <td>76</td>\n",
       "      <td>245</td>\n",
       "      <td>76</td>\n",
       "      <td>240</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>3440</td>\n",
       "      <td>74</td>\n",
       "      <td>2450</td>\n",
       "      <td>74</td>\n",
       "      <td>8.1</td>\n",
       "      <td>74</td>\n",
       "      <td>9.2</td>\n",
       "      <td>74</td>\n",
       "      <td>10.8</td>\n",
       "      <td>...</td>\n",
       "      <td>169</td>\n",
       "      <td>74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>37</td>\n",
       "      <td>74</td>\n",
       "      <td>250</td>\n",
       "      <td>74</td>\n",
       "      <td>245</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>3320</td>\n",
       "      <td>72</td>\n",
       "      <td>2350</td>\n",
       "      <td>72</td>\n",
       "      <td>8.3</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>...</td>\n",
       "      <td>166</td>\n",
       "      <td>72</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>72</td>\n",
       "      <td>255</td>\n",
       "      <td>72</td>\n",
       "      <td>250</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>3200</td>\n",
       "      <td>70</td>\n",
       "      <td>2250</td>\n",
       "      <td>70</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>9.6</td>\n",
       "      <td>70</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>163</td>\n",
       "      <td>70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>33</td>\n",
       "      <td>70</td>\n",
       "      <td>260</td>\n",
       "      <td>70</td>\n",
       "      <td>255</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>3080</td>\n",
       "      <td>68</td>\n",
       "      <td>2150</td>\n",
       "      <td>68</td>\n",
       "      <td>8.7</td>\n",
       "      <td>68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>68</td>\n",
       "      <td>6.6</td>\n",
       "      <td>...</td>\n",
       "      <td>160</td>\n",
       "      <td>68</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68</td>\n",
       "      <td>31</td>\n",
       "      <td>68</td>\n",
       "      <td>265</td>\n",
       "      <td>68</td>\n",
       "      <td>260</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>2960</td>\n",
       "      <td>66</td>\n",
       "      <td>2050</td>\n",
       "      <td>66</td>\n",
       "      <td>8.9</td>\n",
       "      <td>66</td>\n",
       "      <td>10.0</td>\n",
       "      <td>66</td>\n",
       "      <td>5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>157</td>\n",
       "      <td>66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>66</td>\n",
       "      <td>270</td>\n",
       "      <td>66</td>\n",
       "      <td>265</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>2840</td>\n",
       "      <td>64</td>\n",
       "      <td>1950</td>\n",
       "      <td>64</td>\n",
       "      <td>9.1</td>\n",
       "      <td>64</td>\n",
       "      <td>10.2</td>\n",
       "      <td>64</td>\n",
       "      <td>3.8</td>\n",
       "      <td>...</td>\n",
       "      <td>154</td>\n",
       "      <td>64</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64</td>\n",
       "      <td>27</td>\n",
       "      <td>64</td>\n",
       "      <td>275</td>\n",
       "      <td>64</td>\n",
       "      <td>270</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>2720</td>\n",
       "      <td>62</td>\n",
       "      <td>1850</td>\n",
       "      <td>62</td>\n",
       "      <td>9.3</td>\n",
       "      <td>62</td>\n",
       "      <td>10.4</td>\n",
       "      <td>62</td>\n",
       "      <td>2.4</td>\n",
       "      <td>...</td>\n",
       "      <td>151</td>\n",
       "      <td>62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>25</td>\n",
       "      <td>62</td>\n",
       "      <td>280</td>\n",
       "      <td>62</td>\n",
       "      <td>275</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>2600</td>\n",
       "      <td>60</td>\n",
       "      <td>1750</td>\n",
       "      <td>60</td>\n",
       "      <td>9.5</td>\n",
       "      <td>60</td>\n",
       "      <td>10.6</td>\n",
       "      <td>60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>148</td>\n",
       "      <td>60</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60</td>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>285</td>\n",
       "      <td>60</td>\n",
       "      <td>280</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>2470</td>\n",
       "      <td>50</td>\n",
       "      <td>1710</td>\n",
       "      <td>50</td>\n",
       "      <td>9.7</td>\n",
       "      <td>50</td>\n",
       "      <td>10.8</td>\n",
       "      <td>50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>143</td>\n",
       "      <td>50</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "      <td>21</td>\n",
       "      <td>50</td>\n",
       "      <td>305</td>\n",
       "      <td>50</td>\n",
       "      <td>290</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>2340</td>\n",
       "      <td>40</td>\n",
       "      <td>1670</td>\n",
       "      <td>40</td>\n",
       "      <td>9.9</td>\n",
       "      <td>40</td>\n",
       "      <td>11.0</td>\n",
       "      <td>40</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>138</td>\n",
       "      <td>40</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>40</td>\n",
       "      <td>325</td>\n",
       "      <td>40</td>\n",
       "      <td>300</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>2210</td>\n",
       "      <td>30</td>\n",
       "      <td>1630</td>\n",
       "      <td>30</td>\n",
       "      <td>10.1</td>\n",
       "      <td>30</td>\n",
       "      <td>11.2</td>\n",
       "      <td>30</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>133</td>\n",
       "      <td>30</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "      <td>345</td>\n",
       "      <td>30</td>\n",
       "      <td>310</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>2080</td>\n",
       "      <td>20</td>\n",
       "      <td>1590</td>\n",
       "      <td>20</td>\n",
       "      <td>10.3</td>\n",
       "      <td>20</td>\n",
       "      <td>11.4</td>\n",
       "      <td>20</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>128</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>365</td>\n",
       "      <td>20</td>\n",
       "      <td>320</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>1950</td>\n",
       "      <td>10</td>\n",
       "      <td>1550</td>\n",
       "      <td>10</td>\n",
       "      <td>10.5</td>\n",
       "      <td>10</td>\n",
       "      <td>11.6</td>\n",
       "      <td>10</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>123</td>\n",
       "      <td>10</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>385</td>\n",
       "      <td>10</td>\n",
       "      <td>330</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  男肺活量       女肺活量      男50米跑      女50米跑       男体前屈  ...  女跳远       \\\n",
       "            成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩  ...   成绩   分数   \n",
       "0      0  4540  100  3150  100   7.1  100   7.8  100  23.6  ...  204  100   \n",
       "1      1  4420   95  3100   95   7.2   95   7.9   95  21.5  ...  198   95   \n",
       "2      2  4300   90  3050   90   7.3   90   8.0   90  19.4  ...  192   90   \n",
       "3      3  4050   85  2900   85   7.4   85   8.3   85  17.2  ...  185   85   \n",
       "4      4  3800   80  2750   80   7.5   80   8.6   80  15.0  ...  178   80   \n",
       "5      5  3680   78  2650   78   7.7   78   8.8   78  13.6  ...  175   78   \n",
       "6      6  3560   76  2550   76   7.9   76   9.0   76  12.2  ...  172   76   \n",
       "7      7  3440   74  2450   74   8.1   74   9.2   74  10.8  ...  169   74   \n",
       "8      8  3320   72  2350   72   8.3   72   9.4   72   9.4  ...  166   72   \n",
       "9      9  3200   70  2250   70   8.5   70   9.6   70   8.0  ...  163   70   \n",
       "10    10  3080   68  2150   68   8.7   68   9.8   68   6.6  ...  160   68   \n",
       "11    11  2960   66  2050   66   8.9   66  10.0   66   5.2  ...  157   66   \n",
       "12    12  2840   64  1950   64   9.1   64  10.2   64   3.8  ...  154   64   \n",
       "13    13  2720   62  1850   62   9.3   62  10.4   62   2.4  ...  151   62   \n",
       "14    14  2600   60  1750   60   9.5   60  10.6   60   1.0  ...  148   60   \n",
       "15    15  2470   50  1710   50   9.7   50  10.8   50   0.0  ...  143   50   \n",
       "16    16  2340   40  1670   40   9.9   40  11.0   40  -1.0  ...  138   40   \n",
       "17    17  2210   30  1630   30  10.1   30  11.2   30  -2.0  ...  133   30   \n",
       "18    18  2080   20  1590   20  10.3   20  11.4   20  -3.0  ...  128   20   \n",
       "19    19  1950   10  1550   10  10.5   10  11.6   10  -4.0  ...  123   10   \n",
       "\n",
       "     男引体      女仰卧      男1000米跑      女800米跑       \n",
       "      成绩   分数  成绩   分数      成绩   分数     成绩   分数  \n",
       "0   16.0  100  53  100     210  100    204  100  \n",
       "1   15.0   95  51   95     215   95    210   95  \n",
       "2   14.0   90  49   90     220   90    216   90  \n",
       "3   13.0   85  46   85     227   85    223   85  \n",
       "4   12.0   80  43   80     235   80    230   80  \n",
       "5    NaN   78  41   78     240   78    235   78  \n",
       "6   11.0   76  39   76     245   76    240   76  \n",
       "7    NaN   74  37   74     250   74    245   74  \n",
       "8   10.0   72  35   72     255   72    250   72  \n",
       "9    NaN   70  33   70     260   70    255   70  \n",
       "10   9.0   68  31   68     265   68    260   68  \n",
       "11   NaN   66  29   66     270   66    265   66  \n",
       "12   8.0   64  27   64     275   64    270   64  \n",
       "13   NaN   62  25   62     280   62    275   62  \n",
       "14   7.0   60  23   60     285   60    280   60  \n",
       "15   6.0   50  21   50     305   50    290   50  \n",
       "16   5.0   40  19   40     325   40    300   40  \n",
       "17   4.0   30  17   30     345   30    310   30  \n",
       "18   3.0   20  15   20     365   20    320   20  \n",
       "19   2.0   10  13   10     385   10    330   10  \n",
       "\n",
       "[20 rows x 25 columns]"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel('./体侧成绩评分表.xls',header = [0,1])\n",
    "df2.drop_duplicates(inplace = True) # 删除重复数据\n",
    "df2.reset_index(inplace=True) # ⾏索引 重置\n",
    "def convert_str(x):\n",
    "  m,s = x[0:-1].split(\"'\")\n",
    "  return int(m)*60+int(s)  \n",
    "df2.iloc[:,-4]=df2.iloc[:,-4].apply(convert_str)\n",
    "df2.iloc[:,-2]=df2.iloc[:,-2].apply(convert_str)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "4c1a426a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>level_0</th>\n",
       "      <th>index</th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>253.0</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>256.0</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>249.0</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>261.0</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>224.0</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>468</th>\n",
       "      <td>468</td>\n",
       "      <td>471.0</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>298.0</td>\n",
       "      <td>8.76</td>\n",
       "      <td>200.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4533.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>469</th>\n",
       "      <td>469</td>\n",
       "      <td>472.0</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>263.0</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>470</td>\n",
       "      <td>473.0</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>319.0</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>471</td>\n",
       "      <td>474.0</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>205.0</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>472</td>\n",
       "      <td>475.0</td>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>279.0</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>473 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     level_0  index  班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高  \\\n",
       "0          0    NaN   1  男    253.0   8.88  195.0  12.0   1.0  2785.0  170.0   \n",
       "1          1    1.0   1  男    256.0   7.70  225.0  11.0   7.0  3133.0  174.0   \n",
       "2          2    2.0   1  男    249.0   8.45  218.0  14.0   1.0  3901.0  169.0   \n",
       "3          3    3.0   1  男    261.0   8.05  206.0  13.0   1.0  4946.0  183.0   \n",
       "4          4    4.0   1  男    224.0   7.52  210.0  13.0   9.0  3538.0  171.0   \n",
       "..       ...    ...  .. ..      ...    ...    ...   ...   ...     ...    ...   \n",
       "468      468  471.0  17  男    298.0   8.76  200.0  12.0   9.0  4533.0  169.0   \n",
       "469      469  472.0  17  男    263.0   8.27  208.0  10.0   NaN  4647.0  176.0   \n",
       "470      470  473.0  17  男    319.0   9.55  210.0  15.0   6.0  7042.0  177.0   \n",
       "471      471  474.0  17  男    205.0   7.50  252.0  13.0  13.0  5755.0  181.0   \n",
       "472      472  475.0  17  男    279.0   7.81  208.0  14.0  11.0  5688.0  172.0   \n",
       "\n",
       "       体重  BMI  男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  男引体分数  男肺活量分数  \n",
       "0    72.6    0       72.0     66.0   60.0    76.0   10.0    64.0  \n",
       "1    52.7    0       70.0     78.0   76.0    76.0   60.0    70.0  \n",
       "2    46.5    0       74.0     70.0   72.0    80.0   10.0    85.0  \n",
       "3    79.7    0       68.0     74.0   66.0    78.0   10.0     NaN  \n",
       "4    54.7    0       85.0     78.0   68.0    78.0   68.0    76.0  \n",
       "..    ...  ...        ...      ...    ...     ...    ...     ...  \n",
       "468  51.3    0       50.0     66.0   64.0    76.0   68.0   100.0  \n",
       "469  69.5    0       68.0     72.0   68.0    74.0    NaN     NaN  \n",
       "470  76.0    0       40.0     50.0   68.0    80.0   50.0     NaN  \n",
       "471  65.0    0      100.0     80.0   95.0    78.0   85.0     NaN  \n",
       "472  51.7    0       62.0     76.0   68.0    80.0   76.0     NaN  \n",
       "\n",
       "[473 rows x 19 columns]"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def search_score(x):\n",
    "    for index in range(array.shape[0]):\n",
    "        time = array[index,0]\n",
    "        score = array[index,1]\n",
    "        if x <= time:\n",
    "            return score\n",
    "    return np.nan\n",
    "for field in field_list:\n",
    "    if (field in df2) and field != \"index\":\n",
    "        df3 = df2[field]\n",
    "        df3.columns = ['time','score']\n",
    "        df3 = df3.sort_values(by='time')\n",
    "        array = df3.to_numpy()\n",
    "        df1[field+\"分数\"] = df1[field].apply(search_score)\n",
    "df1"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.1"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
